MySQL 数据库

数据库基本命令

DDL 语句

1
2
3
4
5
6
7
8
9
10
# 创建数据库
CREATE DATABASE 数据库名称;
# 展示所有数据库
SHOW DATABASES;
# 删除数据库
DROP DATABASE 数据库名称;
# 使用数据库
USE 数据库名称;
# 展示当前数据库表
SHOW TABLES;

数据库模式

查看模式

1
SELECT @@sql_mode;

严格模式

  1. STRICT_ALL_TABLES : 对所有表开启严格模式（包括事务表和非事务表）
  2. STRICT_TRANS_TABLES : 对事物表开启严格模式
  3. NO_ENGINE_SUBSTITUTION: 严格模式（默认）

设置严格模式:

  1. 临时会话设置 SET SESSION sql_mode=’STRICT_TRANS_TABLESA’; 只对当前会话生效
  2. 配置文件修改模式: 向配置文件中添加 sql_mode=STRICT_ALL_TABLES, 记得重启服务器

a. Windows 系统找 my.ini 配置文件，没有文件需要手动创建
b. linux 系统找 my.cnf 配置文件

数据类型

整形

类型 字节数 取值范围
TINYINT 1个字节 -128~127
SMALLINT 2个字节 -32768~32767
MEDIUMINT 3个字节 -8388608~8388607
INT / INTEGER 4个字节 -2147483648~2147483647
BIGINT 8个字节 -9223372036854775808~9223373036854775807

ZEROFILL 零填充

  1. INT(5)其中的5并不是支持存储值的最大个数，5在此处相当于无效
  2. INT(5) ZEROFILL默认最小存储的位数为5
    1. 如果存储的值为234，不到5位值，则使用零进行填充至5位，最后在记录中存储的值为00234
    2. 如果存储的值为234567，超出5位值，则不做任何处理，最后在记录中存储的值为234567

总结：INT(5)中的5需要配合ZEROFILL一起使用，否则无效

UNSIGNED 无符号

添加UNSIGNED进行无符号化后，最大值翻倍，如TINYINT UNSIGNED的取值范围为（0~255）

类型 字节数 取值范围
TINYINT 1个字节 0~255
SMALLINT 2个字节 0~65535
MEDIUMINT 3个字节 0~16777215
INT/INTEGER 4个字节 0~4294967295
BIGINT 8个字节 0~18446744073709551615

定点型

类型 字节数 取值范围
DECIMAL(M, D) 定点型 变长 精确到小数点后65位
  1. 定点型可以用类型名称后加(M, D)来表示，M和D又称为精度和标度
    1. M表示该值的总共长度
    2. D表示小数点后面的长度
  2. DECIMAL在不指定精度时，默认整数为10，小数为0
  3. 假如DECIMAL(5, 2)
    1. 存入数值为345.474，实际到数据库中的值为：345.47
    2. 存入数值为345.479，实际到数据库中的值为：345.48
    3. 存入数值为1345.479，严格模式下则会爆错，超出了总位数 5

字符串

类型 字节数 取值范围
TINYINT 1个字节 0~255
SMALLINT 2个字节 0~65535
MEDIUMINT 3个字节 0~16777215
INT/INTEGER 4个字节 0~4294967295
BIGINT 8个字节 0~18446744073709551615

日期时间类型

类型 字节数 取值范围
TINYINT 1个字节 0~255
SMALLINT 2个字节 0~65535
MEDIUMINT 3个字节 0~16777215
INT/INTEGER 4个字节 0~4294967295
BIGINT 8个字节 0~18446744073709551615

表

创建表

1
2
3
4
5
6
7
CREATE TABLE 表名(
    列名1 数据类型 [约束1 约束2 ...][COMMENT '备注名'],
    列名2 数据类型 [约束1 约束2 ...][COMMENT '备注名'],
    列名3 数据类型 [约束1 约束2 ...][COMMENT '备注名'],
    .....
    [约束1，约束2，约束3...]
) [COMMENT '表备注名'];

例子:

1
2
3
4
5
6
7
CREATE TABLE `student`(
  `id` INT COMMENT '编号',
  `name` VARCHAR(20) COMMENT '名字',
  `sex` CHAR(1) COMMENT '性别',
  `birthday` DATE COMMENT '出生日期',
  `score` DECIMAL(4, 1) COMMENT '入学成绩'
);

在严格模式下，表的记录必须符合字段的数据类型

约束

Not Null 非空

【列级约束】用**NOT NULL**约束的字段不能为**NULL**值，必须给定具体的数据

1
2
3
4
5
6
7
8
9
10
-- NOT NULL 【列级约束】，该字段的数据不能为空
CREATE TABLE dog(
	`name` VARCHAR(10) NOT NULL,
	`age` INT
);

-- 向表中添加记录
INSERT INTO `dog` VALUES ('qiqi',4);
INSERT INTO `dog` VALUES ('heidou',NULL);
INSERT INTO `dog` VALUES (NULL,5); -- 报错 name 字段不能为NULL值

DEFAULT 默认值

【列级约束】用**DEFAULT**约束的字段，在新增和修改语句中使用**DEFAULT**关键词使用字段声明的默认值数据

1
2
3
4
5
6
7
8
9
-- DEFAULT: 【列级约束】：默认值，可通过DEFAULT设置默认值到数据库表中
CREATE TABLE `cat`(
	`name` VARCHAR(10),
	`sex` CHAR(1) DEFAULT 'M'
);

INSERT INTO `cat` VALUES('ZHAOCAI','W');
INSERT INTO `cat` VALUES('XIAOHUANG','M');
INSERT INTO `cat` VALUES('TANGDOU',DEFAULT); --采用默认值将M添加到表中

UNIQUE 唯一

【列级约束、表级约束】**UNIQUE**约束的字段，具有唯一性，不可重复，**NULL**值除外，**UNIQUE**表级别的约束还可以对多个字段进行联合唯一约束声明

1
2
3
4
5
6
7
8
9
10
11
-- UNIQUE 约束的字段具有唯一性，不能重复(NULL值除外)
-- UNIQUE 【列级约束】
CREATE TABLE `emp`(
	`id` INT(20),
	`name` VARCHAR(10),
	`userId` VARCHAR(10) UNIQUE
);

INSERT INTO `emp` VALUES (1,'Melody','W00001');
INSERT INTO `emp` VALUES (2,'Xuan','W00002');
INSERT INTO `emp` VALUES (3,'Lv','W00003');
1
2
3
4
5
6
7
8
9
10
11
12
13
-- UNIQUE 表级约束的声明 (可自定义约束名称）

CREATE TABLE `person`(
	`name` VARCHAR(10),
	`idCard` VARCHAR(20),
	CONSTRAINT id_Card UNIQUE(`idCard`)
);


INSERT INTO `person` VALUES ('Melody','W00001');
INSERT INTO `person` VALUES ('Xuan','W00002');
INSERT INTO `person` VALUES ('Lv','W00003');
INSERT INTO `person` VALUES ('Marshmello','W00001');
1
2
3
4
5
6
7
8
9
10
11
-- UNIQUE 联合唯一（只能在表级中声明此约束)
CREATE TABLE `dept`(
	`id` INT(10),
	`name` VARCHAR(10),
	`r_name` VARCHAR(10),
	CONSTRAINT `dname_rname` UNIQUE(`name`,`r_name`)
);
-- 相同的区域下不能出现相同的部门
INSERT INTO `dept` VALUES (1,'RESHI','BEIJING');
INSERT INTO `dept` VALUES (2,'RESHI','WUHAN');
INSERT INTO `dept` VALUES (3,'RESHI','BEIJING'); -- 报错

PRIMARY KEY 主键

  1. 主键约束相当于：**UNIQUE**+**NOT NULL**的组合，主键约束列不允许重复，也不允许出现空值
  2. 表可以无主键，如果有主键约束，那么最多允许有一个
  3. 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引
  4. 主键约束是【列级约束】【表级约束】
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # PRIMARY KEY：主键约束
    # 非空且唯一
    # 一张表可以没有主键字段，有的话最多只能有一个
    CREATE TABLE `teacher`(
    	`id` INT PRIMARY KEY, 
    	`name` VARCHAR(10),
    	`age` INT
    );

    INSERT INTO `teacher` VALUES(1,'LiuQi',23); -- 添加成功
    INSERT INTO `teacher` VALUES(2,'WangPo',40); -- 添加成功
    INSERT INTO `teacher` VALUES(1,'WangWei',20); -- 报错，1号已经存在
    INSERT INTO `teacher` VALUES(NULL,'LiXiao',20); -- 报错，id字段不能为Null值
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    -- 主键约束(表级声明方式)
    CREATE TABLE `s_teacher`(
    	`id` INT,
    	`name` VARCHAR(10),
    	`age` INT,
    	PRIMARY KEY(`id`)
    );

    INSERT INTO `s_teacher` VALUES(1,'LiuQi',23); -- 添加成功
    INSERT INTO `s_teacher` VALUES(2,'WangPo',40); -- 添加成功
    INSERT INTO `s_teacher` VALUES(1,'WangWei',20); -- 报错，1号已经存在
    INSERT INTO `s_teacher` VALUES(NULL,'LiXiao',20); -- 报错，id字段不能为Null值

AUTO_INCREMENT 自增

【列级约束】自增约束的字段必须为整形且为主键，可采用**NULL**字段让主键字段自增

1
2
3
4
5
6
7
8
9
10
11
12
# AUTO_INCREMENT 自增约束
# 约束的字段必须是整形，且为主键
CREATE TABLE `s_student`(
	`id` INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(20),
	`age` INT
);

INSERT INTO `s_student` VALUES(1,'LuQi',20); -- 执行成功
INSERT INTO `s_student` VALUES(NULL,'ZhangXiao',25); -- 执行成功 （使用NULL 采用自增值）
INSERT INTO `s_student` VALUES(10,'YangXiao',30); -- 执行成功
INSERT INTO `s_student` VALUES(NULL,'GuanLu',45); -- 执行成功 （使用NULL 采用自增值）

FOREIGN KEY 外键

  1. 【表级约束】**FOREIGN KEY**外键约束的主要表现为主表和子表之间的关系
  2. **FOREIGN KEY**外键字段必须引用的是主表的主键字段，具有外键的表称为子表，具有主键的表称为引用表或父表，当然外键可以引用自己表中的主键
  3. 语法**FOREIGN KEY**(column) **REFERENCES**** **main_table(pri_key_column)
  4. 创建外键约束时，还可以设置**ON DELETE**和**ON UPDATE**时的操作
    1. **RESTRICT**或**NO ACTION**：当主表中删除或更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除或更新
    2. **CASCADE**：当主表中删除或更新对应记录时，首先检查该记录是否有对应外键，如果有则删除或更新外键在子表中的记录
    3. **SET NULL**：当主表中删除或更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为NULL（该外键无NOT NULL约束
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      # FOREIGN KEY 的字段引用的是主表中的主键字段
      -- FOREIGN KEY 的字段值一定在主表中的主键字段字段值中存在
      -- 主表
      CREATE TABLE `region`(
      	`id` INT PRIMARY KEY,
      	`name` VARCHAR(20)
      );

      INSERT INTO `region` VALUES(1,'BeiJing');
      INSERT INTO `region` VALUES(2,'NanJing');
      INSERT INTO `region` VALUES(3,'ChongQing');

      -- 子表
      CREATE TABLE `customer`(
      	`id` INT,
      	`name` VARCHAR(20),
      	`r_id` INT,
      	FOREIGN KEY(r_id) REFERENCES `region`(`id`)
      );

      INSERT INTO `customer` VALUES(1,'LiQiQi',NULL); -- 添加成功
      INSERT INTO `customer` VALUES(2,'WangQiQi',2); -- 添加成功
      INSERT INTO `customer` VALUES(3,'ZhuQiQi',3); -- 添加成功
      INSERT INTO `customer` VALUES(4,'DouQiQi',30); -- 失败: 没有30号区域
      INSERT INTO `customer` VALUES(5,'FuQiQi',2); -- 添加成功
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      CREATE TABLE `region`(
      	`id` INT PRIMARY KEY,
      	`name` VARCHAR(20)
      );

      INSERT INTO `region` VALUES(1,'BeiJing');
      INSERT INTO `region` VALUES(2,'NanJing');
      INSERT INTO `region` VALUES(3,'ChongQing');

      -- 子表
      CREATE TABLE `customer`(
      	`id` INT,
      	`name` VARCHAR(20),
      	`r_id` INT,
      	FOREIGN KEY(r_id) REFERENCES `region`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
      );

      INSERT INTO `customer` VALUES(1,'LiQiQi',NULL); -- 添加成功
      INSERT INTO `customer` VALUES(2,'WangQiQi',2); -- 添加成功
      INSERT INTO `customer` VALUES(3,'ZhuQiQi',3); -- 添加成功
      INSERT INTO `customer` VALUES(5,'FuQiQi',2); -- 添加成功
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      -- -------------------------------------------------SET NULL-----------------------------------
      CREATE TABLE `region`(
      	`id` INT PRIMARY KEY,
      	`name` VARCHAR(20)
      );

      INSERT INTO `region` VALUES(1,'BeiJing');
      INSERT INTO `region` VALUES(2,'NanJing');
      INSERT INTO `region` VALUES(3,'ChongQing');

      -- 子表
      CREATE TABLE `customer`(
      	`id` INT,
      	`name` VARCHAR(20),
      	`r_id` INT,
      	FOREIGN KEY(r_id) REFERENCES `region`(`id`) ON DELETE SET NULL ON UPDATE SET NULL
      );

      INSERT INTO `customer` VALUES(1,'LiQiQi',NULL); -- 添加成功
      INSERT INTO `customer` VALUES(2,'WangQiQi',2); -- 添加成功
      INSERT INTO `customer` VALUES(3,'ZhuQiQi',3); -- 添加成功
      INSERT INTO `customer` VALUES(5,'FuQiQi',2); -- 添加成功
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      -- ------------------------------------------------------------------------------------
      CREATE TABLE `region`(
      	`id` INT PRIMARY KEY,
      	`name` VARCHAR(20)
      );

      INSERT INTO `region` VALUES(1,'BeiJing');
      INSERT INTO `region` VALUES(2,'NanJing');
      INSERT INTO `region` VALUES(3,'ChongQing');

      -- 子表
      CREATE TABLE `customer`(
      	`id` INT,
      	`name` VARCHAR(20),
      	`r_id` INT,
      	FOREIGN KEY(r_id) REFERENCES `region`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
      );

      INSERT INTO `customer` VALUES(1,'LiQiQi',NULL); -- 添加成功
      INSERT INTO `customer` VALUES(2,'WangQiQi',2); -- 添加成功
      INSERT INTO `customer` VALUES(3,'ZhuQiQi',3); -- 添加成功
      INSERT INTO `customer` VALUES(5,'FuQiQi',2); -- 添加成功

删除表

  1. 语法：
    1. **DROP TABLE**tableName;
    2. **DROP TABLE IF EXISTS**tableName;
  2. 删除表时，需要注意，该表有没有被其他表的外键引用，否则无法删除
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 直接删除表格
    DROP TABLE `cat`;

    -- 如果表格存在则删除
    DROP TABLE IF EXISTS `cats`;

    -- 删除表格时，需注意外键是否绑定此表，如果绑定则会报错
    -- 解决办法1：先删除子表，再删除主表
    -- 解决办法2：先删除子表中的外键约束，再删除主表
    DROP TABLE IF EXISTS `customer`;
    DROP TABLE IF EXISTS `region`;

修改表

新增字段

新增字段语法：ALTER TABLEtableName**ADD newColumndataType[constraints] [FIRST | AFTER **column];

  1. tableName：表名
  2. newColumn：新字段
  3. dataType：数据类型
  4. constraints：约束类型
  5. FIRST：该字段为表中第一个字段
  6. **AFTER **column：放到哪个字段后面
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    CREATE TABLE `a_student`(
    	`id` INT,
    	`name` VARCHAR(20)
    );

    INSERT INTO `a_student` VALUES (1,'LiuQi');
    INSERT INTO `a_student` VALUES (2,'WangQi');

    -- 新增一个字段
    ALTER TABLE `a_student` ADD `age` INT(3) NOT NULL;
    -- 新增一个字段（字符串类型的数据 加上NOT NULL），原始记录为空字符串
    ALTER TABLE `a_student` ADD `address` VARCHAR(20) NOT NULL;
    -- 新增一个字段（数整形类型的数据 加上NOT NULL）
    ALTER TABLE `a_student` ADD `score` INT NOT NULL;
    -- 新增一个字段（字符类型的数据 在`name`字段之后）
    ALTER TABLE `a_student` ADD `phone` CHAR(11) AFTER `name`;
    -- 新增一个字段（时间类型的数据 为该表的第一个字段）
    ALTER TABLE `a_student` ADD `start_time` DATETIME FIRST;

重写字段

重写字段是重新去声明该字段，如果该字段修改前存在表级约束如UNIQUE，则不会被删除
**MODIFY**：

  1. 可以【删除增加列级约束，增加表级约束，字段位置，数据类型】
  2. 语法：**ALTER TABLE **tableName** MODIFY **column1dataType[constraintType][ **FIRST** | **AFTER **column2];

**CHANGE**：

  1. 可以【删除增加列级约束，增加表级约束，字段位置，数据类型，修改列的名字】
  2. 语法：**ALTER TABLE **tableName** CHANGE **oldColumn newColumndataType[constraintType][ **FIRST** | **AFTER **column];
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE TABLE `b_student`(
    	`id` INT,
    	`name` VARCHAR(20) NOT NULL UNIQUE
    );

    INSERT INTO `b_student` VALUES (1,'LiuQi');
    INSERT INTO `b_student` VALUES (2,'WangQi');

    -- 修改表中的字段 （重新命名该字段）
    ALTER TABLE `b_student` MODIFY `id` TINYINT;
    -- 修改表中的字段 （表级约束如 UNIQUE 没有被移除）
    ALTER TABLE `b_student` MODIFY `name` VARCHAR(15) NOT NULL;
    -- 修改表中的字段 （重命名表中字段的名称： CHANGE）
    ALTER TABLE `b_student` CHANGE `name` `stu_name` VARCHAR(20)

删除字段

  1. 语法：**ALTER TABLE **tableName** DROP **columnName;
    1. tableName：表名
    2. columnName：删除的字段名
  2. 删除字段的时候，需要考虑删除的字段是否被外键约束约束了，如果是则无法删除
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    # 删除字段的时候 需要考虑被删除的字段是否被外键约束
    CREATE TABLE `c_teacher`(
    	`id` INT(20) PRIMARY KEY,
    	`name` VARCHAR(10)
    );

    INSERT INTO `c_teacher` VALUES (1,'LiQi');
    INSERT INTO `c_teacher` VALUES (2,'WangWei');

    CREATE TABLE `c_student`(
    	`id` INT(10) PRIMARY KEY AUTO_INCREMENT,
    	`name` VARCHAR(10) NOT NULL,
    	`t_id` INT NOT NULL,
    	FOREIGN KEY(t_id) REFERENCES `c_teacher`(id)
    );

    -- 删除表中的字段
    ALTER TABLE `c_student` DROP `name`; 
    -- 删除表中的字段 （不能删除，报错：违法了外键约束）
    ALTER TABLE `c_student` DROP `t_id`;

添加删除主键约束

  1. 因为PRIMARY KEY约束是也是【列级约束】，所以可以使用重写字段来添加：MODIFY和CHANGE
  2. 添加主键约束【表级约束】（推荐）
    1. 语法：**ALTER TABLE**tableName**ADD **[**CONSTRAINT **indexName] **PRIMARY KEY**(columName);
    2. 语法描述 – tableName：表名，indexName：约束名，columnName：为哪个或哪些字段添加主键约束
  3. 删除表中的主键约束
    1. 语法：**ALTER TABLE**tableName**DROP PRIMARY KEY**;
    2. 语法描述 –tableName：表名

提示：为字段添加主键约束前，必须要考虑以下几点

  1. 该表如果已存在主键字段，则无法添加主键约束
  2. 该字段在记录中的出现NULL，则无法添加主键约束
  3. 该字段在记录中的出现重复值，则无法添加主键约束